﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Diagnostics;
using LOGISTCBUS.Entities;

namespace LOGISTCBUS.DAO
{
    public class VehicleDAO : BaseDAO
    {
        #region public methods
        public Boolean insert(Vehicle v)
        {
            var db = new Db();
            db.Connect();
            String sql = "insert into vehicles (number_plate, year, mark, model, description) ";
            sql += "values (@number_plate, @year, @mark, @model, @description);";
            var parameters = new Dictionary<String, Object>();
            parameters["@number_plate"] = v.NumberPlate;
            parameters["@year"] = v.Year;
            parameters["@mark"] = v.Mark;
            parameters["@model"] = v.Model;
            parameters["@description"] = v.Description;
            try
            {
                var sucess = db.Execute(sql, parameters);
                db.Commit();
                db.Disconnect();
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                db.Rollback();
                db.Disconnect();
                return false;
                throw ex;
            }
        }

        public Boolean update(Vehicle v)
        {
            var db = new Db();
            db.Connect();
            String sql = "update vehicles set number_plate = @number_plate, year = @year, mark = @mark, model = @model, description = @description where id= @id;";
            var parameters = new Dictionary<String, Object>();
            parameters["@number_plate"] = v.NumberPlate;
            parameters["@year"] = v.Year;
            parameters["@mark"] = v.Mark;
            parameters["@model"] = v.Model;
            parameters["@description"] = v.Description;
            parameters["@id"] = v.Id;
            try
            {
                var sucess = db.Execute(sql, parameters);
                db.Commit();
                db.Disconnect();
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                db.Rollback();
                db.Disconnect();
                return false;
                throw ex;
            }
        }

        public Vehicle selectById(Int32 id)
        {
            var db = new Db();
            db.Connect();
            var dt = db.Select("select * from vehicles where id = " + id);
            db.Disconnect();
            if (dt == null || dt.Rows.Count <= 0)
                return null;
            DataRow r = dt.Rows[0];
            var obj = new Vehicle()
            {
                Id = Convert.ToInt32(r["id"]),
                NumberPlate = r["number_plate"].ToString(),
                Year = Convert.ToInt32(r["year"]),
                Mark = r["mark"].ToString(),
                Model = r["model"].ToString(),
                Description = r["description"].ToString(),
                Removed = Convert.ToBoolean(r["removed"])
            };
            return obj;
        }

        public List<Vehicle> select()
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Vehicle>();
            var dt = db.Select("select * from vehicles where removed = 0 order by number_plate");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Vehicle()
                {
                    Id = Convert.ToInt32(r["id"]),
                    NumberPlate = r["number_plate"].ToString(),
                    Year = Convert.ToInt32(r["year"]),
                    Mark = r["mark"].ToString(),
                    Model = r["model"].ToString(),
                    Description = r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public List<Vehicle> select(String numberPlate, String mark, String model, Int32 page, Int32 pageSize)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Vehicle>();
            var sql = "select * from vehicles where removed = 0";
            if (!String.IsNullOrEmpty(numberPlate))
                sql += " and number_plate like '%" + numberPlate + "%'";
            if (!String.IsNullOrEmpty(mark))
                sql += " and mark like '%" + mark + "%'";
            if (!String.IsNullOrEmpty(model))
                sql += " and model like '%" + model + "%'";
            sql += " order by number_plate";
            var dt = db.Select(sql);
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Vehicle()
                {
                    Id = Convert.ToInt32(r["id"]),
                    NumberPlate = r["number_plate"].ToString(),
                    Year = Convert.ToInt32(r["year"]),
                    Mark = r["mark"].ToString(),
                    Model = r["model"].ToString(),
                    Description = r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst.Skip((page - 1) * pageSize).Take(pageSize).ToList();
        }

        public Int32 getCount(String numberPlate, String mark, String model)
        {
            var count = 0;
            var db = new Db();
            db.Connect();
            var lst = new List<Vehicle>();
            var sql = "select count(*) as qtd from vehicles where removed = 0";
            if (!String.IsNullOrEmpty(numberPlate))
                sql += " and number_plate like '%" + numberPlate + "%'";
            if (!String.IsNullOrEmpty(mark))
                sql += " and mark like '%" + mark + "%'";
            if (!String.IsNullOrEmpty(model))
                sql += " and model like '%" + model + "%'";
            var dt = db.Select(sql);
            db.Disconnect();
            if (dt != null && dt.Rows.Count > 0)
            {
                var r = dt.Rows[0];
                count = Convert.ToInt32(r["qtd"]);
            }
            return count;
        }

        public List<Vehicle> select(String filter)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Vehicle>();
            var dt = db.Select("select * from vehicles where removed = 0 and " + getConditions(filter, "number_plate") + " order by number_plate");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Vehicle()
                {
                    Id = Convert.ToInt32(r["id"]),
                    NumberPlate = r["number_plate"].ToString(),
                    Year = Convert.ToInt32(r["year"]),
                    Mark = r["mark"].ToString(),
                    Model = r["model"].ToString(),
                    Description = r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public List<Vehicle> selectVehicles(Int32 idTrip, Boolean hasNot)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Vehicle>();
            var sql = "select * from vehicles v where" + (hasNot ? " v.removed = 0 and " : " ") + "v.id" + (hasNot ? " not " : " ") + "in (select vt.id_vehicle from vehicle_trips vt where vt.removed = 0 and vt.id_trip = " + idTrip + ") order by v.number_plate";
            var dt = db.Select(sql);
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Vehicle()
                {
                    Id = Convert.ToInt32(r["id"]),
                    NumberPlate = r["number_plate"].ToString(),
                    Year = Convert.ToInt32(r["year"]),
                    Mark = r["mark"] is DBNull ? string.Empty : r["mark"].ToString(),
                    Model = r["model"] is DBNull ? string.Empty : r["model"].ToString(),
                    Description = r["description"] is DBNull ? string.Empty : r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public Boolean delete(Vehicle v)
        {
            var db = new Db();
            db.Connect();
            String sql = getSQLDeleteUpdate("vehicles", "number_plate");
            var parameters = new Dictionary<String, Object>();
            parameters["@id"] = v.Id;
            var sucess = db.Execute(sql, parameters);
            db.Commit();
            db.Disconnect();
            return sucess;
        }

        public Boolean delete(List<Vehicle> lst)
        {
            var db = new Db();
            db.Connect();
            var sucess = true;
            foreach (var v in lst)
            {
                String sql = getSQLDeleteUpdate("vehicles", "number_plate");
                var parameters = new Dictionary<String, Object>();
                parameters["@id"] = v.Id;
                sucess = db.Execute(sql, parameters);
            }
            db.Commit();
            db.Disconnect();
            return sucess;
        }
        #endregion
    }
}
